﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BBD_DAL.Core;
using BBD_DAL.Entities;


namespace DBFiller.Controler
{
	public class Filler
	{
		private Dictionary<string, long> states;
		private Dictionary<string, long> regions;
		private Dictionary<string, long> cities;
		private Dictionary<string, long> schools;
		private Dictionary<int, long> questions;

		private DataContext dataContext;

		public Filler(string connectionString)
		{
			dataContext = new DataContext(connectionString);
			states = new Dictionary<string, long>();
			regions = new Dictionary<string, long>();
			cities = new Dictionary<string, long>();
			schools = new Dictionary<string, long>();
			questions = new Dictionary<int, long>();
		}

		public void ClearDatabase()
		{
			dataContext.AnswerDao.Delete();
			dataContext.QuestionDao.Delete();
			dataContext.QuestionnaireDao.Delete();
			dataContext.SchoolDao.Delete();
			dataContext.CityDao.Delete();
			dataContext.RegionsDao.Delete();
			dataContext.StateDao.Delete();
		}

		public void InsertRegions()
		{
			regions.Add("pn", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.North }).Id);
			regions.Add("pl", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.South }).Id);
			regions.Add("z", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.West }).Id);
			regions.Add("w", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.East }).Id);
			regions.Add("c", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.Central }).Id);
			regions.Add("pnz", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.North | Regions.West }).Id);
			regions.Add("pnw", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.North | Regions.East }).Id);
			regions.Add("plz", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.South | Regions.West }).Id);
			regions.Add("plw", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.South | Regions.East }).Id);
			regions.Add("u", dataContext.RegionsDao.Insert(new Region() { RegionCode = Regions.Unknown }).Id);
		}

		public void InsertStates()
		{
			states.Add("dolnośląskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pl"] }, StateName = States.Dolnoslaskie }).Id);
			states.Add("kujawskopomorskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pnz"] }, StateName = States.KujawskoPomorskie }).Id);
			states.Add("lubelskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["w"] }, StateName = States.Lubelskie }).Id);
			states.Add("lubuskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pnz"] }, StateName = States.Lubuskie }).Id);
			states.Add("łódzkie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["c"] }, StateName = States.Lodzkie }).Id);
			states.Add("mazowieckie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["c"] }, StateName = States.Mazowieckie }).Id);
			states.Add("opolskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pl"] }, StateName = States.Opolskie }).Id);
			states.Add("podkarpackie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["w"] }, StateName = States.Podkarpackie }).Id);
			states.Add("podlaskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["w"] }, StateName = States.Podlaskie }).Id);
			states.Add("pomorskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pnz"] }, StateName = States.Pomorskie }).Id);
			states.Add("śląskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pl"] }, StateName = States.Slaskie }).Id);
			states.Add("świętokrzyskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["w"] }, StateName = States.Swietokrzyskie }).Id);
			states.Add("warmińskomazurskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["w"] }, StateName = States.WarminskoMazurskie }).Id);
			states.Add("wielkopolskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pnz"] }, StateName = States.Wielkopolskie }).Id);
			states.Add("zachodniopomorskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pnz"] }, StateName = States.Zachodniopomorskie }).Id);
			states.Add("małopolskie", dataContext.StateDao.Insert(new State() { Region = new Region() { Id = regions["pl"] }, StateName = States.Malopolskie }).Id);
		}

		public void InsertQuestions()
		{
			questions.Add(1, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy wypalił 1 papierosa" }).Id);
			questions.Add(2, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy wypalił 100 papierosów" }).Id);
			questions.Add(3, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy palił w ciągu ostatnich 30 dni" }).Id);
			questions.Add(4, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy chociaż raz paliłeś fajkę" }).Id);
			questions.Add(5, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy paliłeś fakję w ciągu ost 30 dni" }).Id);
			questions.Add(6, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy podczas palenia pijesz alkohol" }).Id);
			questions.Add(7, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy słyszałeś o tabace" }).Id);
			questions.Add(8, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy wciągałeś tabakę do nosa" }).Id);
			questions.Add(9, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy wciągałeś w ciągu ost 30 dni" }).Id);
			questions.Add(10, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy podczas wciągania pijesz alkohol" }).Id);
			questions.Add(11, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy słyszałeś o tytoniu do żucia" }).Id);
			questions.Add(12, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy chociaż raz żułeś tytoń" }).Id);
			questions.Add(13, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy podczas żucia pijesz alkohol" }).Id);
			questions.Add(14, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy słyszałeś o epapierosach" }).Id);
			questions.Add(15, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy próbowałeś e-papierosa" }).Id);
			questions.Add(16, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy stosujesz obecnie e-papierosa" }).Id);
			questions.Add(17, dataContext.QuestionDao.Insert(new Question() { QuestionText = "Czy w ostatnim roku brałeś udział w jakimś programie antynikotynowym" }).Id);
		}

		public void AddRow(string data)
		{

			string[] cells = data.Split(new char[] { '\t' });

			if (cells.Length < 7)
				return;
			long cityId;

			if (!cities.TryGetValue(cells[3], out cityId))
			{
				cityId = dataContext.CityDao.Insert(new City() { CityName = cells[3], State = new State() { Id = states[cells[1]] } }).Id;
				cities.Add(cells[3], cityId);
			}

			long schoolId;

			if (!schools.TryGetValue(cells[4], out schoolId))
			{
				schoolId = dataContext.SchoolDao.Insert(new School() { SchoolName = cells[4], City = new City() { Id = cityId }, Date = DateTime.ParseExact(cells[0], "MM-yyyy", null) }).Id;
				schools.Add(cells[4], schoolId);
			}

			long questionnaireId = dataContext.QuestionnaireDao.Insert(new Questionnaire() { School = new School() { Id = schoolId }, Age = string.IsNullOrEmpty(cells[5]) ? (short?)null : Int16.Parse(cells[5]), Sex = Sex.NotDefined.GetSex(cells[6]) }).Id;

			int counter = cells.Length - 7;

			for (int i = 0; i < counter; i++)
			{
				dataContext.AnswerDao.Insert(new Answer()
				{
					GivenAnswer = (string.IsNullOrEmpty(cells[7 + i].ToString()) ? (bool?)null : (cells[7 + i].ToString().ToLower().Equals("tak") ? true : false)),
					Question = new Question() { Id = (long)i + 1 },
					Questionnaire = new Questionnaire() { Id = questionnaireId }
				});
			}
		}
	}
}
